clear
set more off, perm
cd /Users/zachbrown/Projects/PriceTransparency/Data/

// Open radiology visits
guse build/radiology_visit_1.dta, clear

keep rec_id_radio from_date year member_key primary_ins member_zip serv_prov_key serv_prov_zip bill_prov_key

// Merge on additional membership zip info
mdesc member_zip
gen yearmo = year*100 + month(from_date)
gen coverage_medical=1
rename member_zip member_zip_clm
count

tempfile tmpdata
shell nice gunzip build/members_clean.dta -c > `tmpdata'
merge m:1 member_key yearmo coverage_medical primary_ins using `tmpdata' ///
	,keepusing(member_key yearmo std_product_type insurance_type tier primary_ins coverage_medical member_zip payernm_long payernm_short payer_on_web payer_id mm_start_yearmo mm_end_yearmo)
drop if _merge==2

// Use membership zip if claim zip is missing (could try reverse)
replace member_zip = member_zip_clm if member_zip==.
drop _merge coverage_medical primary_ins yearmo member_zip_clm year

// Merge on billing provider zip
rename bill_prov_key prov_key
merge m:1 prov_key using build/provider_build.dta, keepusing(prov_clinic_zip)
drop if _merge==2
drop _merge
rename prov_clinic_zip bill_prov_zip
rename prov_key bill_prov_key

// Fill in zip code
mdesc serv_prov_zip
bys serv_prov_key: egen zip_tmp = mode(serv_prov_zip)
replace serv_prov_zip = zip_tmp if serv_prov_zip==. & zip_tmp~=.
drop zip_tmp
mdesc serv_prov_zip

mdesc bill_prov_zip
bys bill_prov_key: egen zip_tmp = mode(bill_prov_zip)
replace bill_prov_zip = zip_tmp if bill_prov_zip==. & zip_tmp~=.
drop zip_tmp

bys bill_prov_key: egen zip_tmp = mode(bill_prov_zip)
replace bill_prov_zip = zip_tmp if bill_prov_zip==. & zip_tmp~=.
drop zip_tmp
mdesc bill_prov_zip

// Calculate distance
rename serv_prov_zip prov_clinic_zip
sort member_zip prov_clinic_zip
tempfile tmpdata
shell nice gunzip dist/sf12010zctadist100miles_clean.dta -c > `tmpdata'
merge m:1 member_zip prov_clinic_zip using `tmpdata', sorted
drop if _merge==2
drop _merge
mdesc dist_mi
summ dist_mi
rename dist_mi serv_dist_mi
rename prov_clinic_zip serv_prov_zip

rename bill_prov_zip prov_clinic_zip
sort member_zip prov_clinic_zip
tempfile tmpdata
shell nice gunzip dist/sf12010zctadist100miles_clean.dta -c > `tmpdata'
merge m:1 member_zip prov_clinic_zip using `tmpdata', sorted
drop if _merge==2
drop _merge
mdesc dist_mi
summ dist_mi
rename dist_mi bill_dist_mi
rename prov_clinic_zip bill_prov_zip

duplicates report from_date member_key bill_prov_key serv_prov_key

keep rec_id_radio serv_dist_mi bill_dist_mi bill_prov_zip
sort rec_id_radio
compress
gsave dist/radiology_dist.dta, replace


